create Table TaskInstanceData(
instance_id varchar(40) not null primary key clustered,
definition_id varchar(40) not null,
task_id varchar(40) not null,
process_id varchar(40) null,
parent_task_id varchar(40) null,
version int not null,
status int not null,
start_date datetime not null,
finish_date datetime null,
last_modified datetime not null,
task_data ntext null,
scheduling_status int not null,
task_type varchar(50) null
last_index_date datetime null
)
go
grant select, insert, update, delete on taskinstancedata to nginn
go

CREATE TABLE [dbo].[MQueue](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[from_endpoint] [varchar](50) NOT NULL,
	[to_endpoint] [varchar](50) NOT NULL,
	[subqueue] [char](1) NOT NULL,
	[insert_time] [datetime] NOT NULL,
	[last_processed] [datetime] NULL,
	[retry_count] [int] NOT NULL,
	[retry_time] [datetime] NOT NULL,
	[error_info] [text] NULL,
	[correlation_id] [varchar](100) NULL,
	[label] [varchar](100) NULL,
	[msg_text] varchar(max) NULL,
	[msg_headers] varchar(max) null,
	[unique_id] varchar(40) null
 CONSTRAINT [PK_MQueue] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
grant select, insert, update, delete on MQueue to nginn
go
create index IDX_MQueue_subqueue_retry_time_id on MQueue(subqueue, retry_time, id)
go

U

CREATE TABLE [dbo].[MessageCorrelationMapping](
	[message_id] [varchar](100) NOT NULL,
	[instance_id] [varchar](100) NOT NULL,
	[insert_date] [datetime] NOT NULL
) ON [PRIMARY]
go
create index idx_MessageCorrelationMapping_1 on MessageCorrelationMapping(message_id, instance_id)
go
create index idx_MessageCorrelationMapping_2 on MessageCorrelationmapping(instance_id)
go

CREATE TABLE [dbo].[NGTaskStatus](
	[id] [int] NOT NULL,
	[name] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

create index IDX_TaskInstanceData_definition_id_task_id on TaskInstanceData(definition_id, task_id)
go
create index IDX_TaskInstanceData_process_id on TaskInstanceData(process_id)
go
create index IDX_TaskInstanceData_parent_task_id on TaskInstanceData(parent_task_id)
go
create index IDX_TaskInstanceData_scheduling_status_status_start_date on TaskInstanceData(scheduling_status, status, start_date)
go

insert into ngtaskstatus values(1, 'Enabled')
insert into ngtaskstatus values(2, 'Selected')
insert into ngtaskstatus values(3, 'Completed')
insert into ngtaskstatus values(4, 'Cancelled')
insert into ngtaskstatus values(5, 'Failed')

go

